Chapter 15. Parallel Query
- 為了更快回應查詢 PostgreSQL 設計的 query plan 可利用多個 CPU
- 很多查詢沒辦法受益於 parallel query 因為現在實作的限制,或是沒能找到比 serial 更快的 query plan
- 通常受益最多的查詢
回傳少數 row
,但觸及大量資料
15.1. How Parallel Query Works
EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
-- QUERY PLAN
-- -----------------------------------------------------------------------------------
-- Gather (cost=1.00..2161.93 rows=1 width=97)
-- Workers Planned: 2
-- -> Parallel Seq Scan on pgbench_accounts (cost=0.00..2160.83 rows=1 width=97)
-- Filter: (filler ~~ '%x%'::text)
-- (4 rows)
plan tree
- 當
optimizer
判定 parallel query 是比較快的策略,就會建立包含Gather
或Gather Merge
node 的 query plan - 在所有情況下
Gather
orGather Merge
node 只會有剛好一個child plan
node,child plan
是plan
的一部分,會被平行執行 - 當
Gather
orGather Merge
node 在plan
tree 的頂端,整個 query 都會平行執行;如果是在 child node ,則只有其底下的 plan 才會平行執行 - 在範例中的 query 只有用到一張 table ,除了
Gather
node 本身之外只有一個plan
node; 這個plan
node 是Gather
node 的 child ,因此他會平行執行
- 當
worker
- 使用
EXPLAIN
可以看到 plan 的 worker 數量,當 query 處理到Gather
node 時,會產生該數量的 background worker processes - planner 最多會產生的 worker 數量是參數
max_parallel_workers_per_gather
; background workers 的總數受參數max_worker_processes
和max_parallel_workers
限制 - 有可能會發生,使用的 worker 數量比 plan 的還要少,甚至沒有 worker ,所以可能會因為造成效能很差。
- 如果這個情況常發生可以考慮加大
max_worker_processes
和max_parallel_workers
以增加 worker ;或是減小max_parallel_workers_per_gather
減少單一 plan 要的 worker 數量 - 每個 background worker process 都會執行一部分 query 包括 leader,但是 leader 還需要讀取 worker 產生的所有資料(tuple)
- 當產生的 tuple 數量少的時候, leader 行為會像 worker 幫忙加速查詢;如果產生的 tuple 數量多, leader 幾乎會整個被指派去讀 worker 產生的 tuple 及執行
Gather
node 或Gather Merge
node 上層的 plan node 所要求的工作,在這個狀況, leader 只會執行很少的平行工作。 - When the node at the top of the parallel portion of the plan is
Gather Merge
rather than Gather, it indicates that each process executing the parallel portion of the plan is producing tuplesin sorted order
, and that the leader is performing an order-preserving merge. In contrast,Gather
reads tuples from the workers in whatever order is convenient, destroying any sort order that may have existed.
- 使用
註:
- worker 是 single-thread process
Gather Merge
會在使用到ORDER BY
語句的 parallel query 出現,使用binary heap
。- 使用 parallel 時 plan tree 就會出現
Gather/(Merge)
node ,該 node 只會有一個 child , child 是 parallel 的 max_parallel_workers_per_gather
- Type:integer
- Default:2
- Min:0 / Max:1024
- Restart:false
- 限制單一
Gather / (Merge)
node 的 worker 數量 - request 的 worker 數量可能不夠,所以 plan 效率可能會比較差
- 設為 0 會停用 parallel query
- 一個 parallel query 如果使用了 N 個 worker 代表其可能使用了最多 N+1 倍的資源
max_worker_processes
- Type:integer
- Default:8
- Min:0 / Max:262143
- Restart:true
- 限制最大的 background processes 數量
- standby server 的數值不能小於 primary server ,不然會無法 query
- 建議一併調整
max_parallel_workers
max_parallel_maintenance_workers
max_parallel_workers_per_gather
- parallel worker 會從 pool 取得
max_parallel_workers
- Type:integer
- Default:8
- Min:0 / Max:1024
- Restart:false
- 限制同一時間 database cluster 可用的 parallel worker processes。不能大於
max_worker_processes
- parallel worker 的數量被此參數限制
- 加大時建議一併調整
max_parallel_maintenance_workers
max_parallel_workers_per_gather
- 設定的比
max_worker_processes
是沒有用的
15.2. When Can Parallel Query Be Used?
max_parallel_workers_per_gather
> 0- 當 OS 是
single-user mode
時不會使用 parallel query,因為沒辦法用 background worker - 以下情況不會使用 parallel query:
- query 寫資料或是鎖定任何 row 的時候。如果 query 包含修改資料的操作或是 CTE 不行。以下為搭配 SELECT 使用的例外:
CREATE TABLE ... AS
SELECT INTO
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
- 當 query 可能會被暫停的時候。例如
DECLARE CURSOR
、FOR x IN query LOOP .. END LOOP
不可能使用 parallel plan。 - query 用到標記為
PARALLEL UNSAFE
的 function 的時候。 - 當 query 已經在平行的 query 的時候。例如 parallel query 呼叫 function 去做 query 不會使用 parallel plan。因為這可能會需要很多 process ,所以實作會保持目前的限制。
- query 寫資料或是鎖定任何 row 的時候。如果 query 包含修改資料的操作或是 CTE 不行。以下為搭配 SELECT 使用的例外:
- 就算產生了 parallel query plan ,但是在執行時可能無法執行該 parallel query , leader 會自行處理,就算是 Gather node 還沒出現。會在以下情形發生:
- 若 parallel 會使 background workers 數量超過
max_worker_processes
- 若 parallel 會使執行中的 background workers 數量超過
max_parallel_workers
- 若 parallel 會使 background workers 數量超過
15.3.1. Parallel Scans
parallel sequential scan
: table blocks 會被劃分成多個範圍,在 process 之間共用。 每個 worker process 會在完成目前的 table blocks 範圍才會再去拿別的 block。parallel bitmap heap scan
: 有一個 process 被選為 leader,他會 scan 一個或是多個 index ,還有建立 bitmap 指示哪些 table block 需要被 scan 。 接著這些 block 會被分配給 process 執行 parallel sequential scan 。 也就是說 heap scan 是平行的,底層的 index scan 不是。parallel index scan
或parallel index-only scan
: process 輪流從 index 讀取資料。目前parallel index scans
只有btree index
可用 。每個 process 都會認領一個 index block 然後 scan 及回傳所有該 block 參照的 tuple ,同時其他 process 可以回傳不同 index block 的 tuple 。在每個 worker process 內, parallel btree scan 的結果都會排序。
15.3.2. Parallel Joins
與 non-parallel plan 一樣,可以使用 nested loop
, hash join
, merge join
將 table 與一張或是多張表做 join 。 如果可以安全執行的話,inner side 可以是任何 planner 支援的 non-parallel plan 。有的 join 其 inner side 也可以是 parallel plan 。
nested loop join
: inner side 一律為 non-parallel 。如果 inner side 是 index scan 效率會比較好,因為外部可以將 index 查找分配到多個 processmerge join
: inner side 一律為 non-parallel 。效率可能不好,尤其是要排序時,因為工作和結果會在每個 process 中重複。hash join
(without the "parallel" prefix): inner side 每個 process 都會複製一份 hash table 。 如果 hash table 很大或是 plan 開銷大,這樣效率會很低。 在hash join
中 inner side 是一個 parallel hash ,它把 shared hash table 分配到多個 process。
15.3.3. Parallel Aggregation
PostgreSQL 透過兩個階段的 aggregation 去支援平行。第一步,每個 query 的 process 執行一個 aggregation step ,產生一部分的結果,這在 plan 中是一個 Partial Aggregate
node 。第二步,部分的結果透過 Gather
或 Gather Merge
傳送到 leader 。最後 leader 重新 aggregate 來自 workers 的結果作為最終結果。這在 plan 中是一個 Finalize Aggregate
node 。
因為 Finalize Aggregate
node 是在 leader process 執行, query planner 不喜歡分組數相對大量的 query 。例如最壞的情形是, Finalize Aggregate
node 看到的分組數量和 Partial Aggregate
的 input rows 數量,這種情形,使用 parallel aggregation 明顯效能很差,所以 query planner 不會傾向使用 parallel aggregate 的做法。
Parallel aggregation 不是在所有情況下都支援,每個 aggregate 必須是 safe
for parallelism 且必須有一個 combine function 。如果 aggregate 有一個 internal 類型的 transition state ,他必需要有 serialization function 和 deserialization function 。 詳細資訊可看 CREATE AGGREGATE
。 如果 aggregate function call 有 DISTINCT
or ORDER BY
不會支援 parallel aggregation , ordered set aggregates 或是 GROUPING SETS
也不支援。只有 query 的所有 join 也是 plan 的平行部分之一才支援。
15.3.4. Parallel Append
當 PostgreSQL 需要結合多個來源的 row 為一個 result set 時,會使用 Append
plan node 或 MergeAppend
plan node ,使用 UNION ALL
或是掃描 partitioned table 的時候會常發生,就像是這些 node 可以被用在其他 plan 一樣,也可以用在 parallel plan ,不過在 parallel plan , planner 使用的是 Parallel Append
node 。
當 Append
node 被使用在 parallel plan 時,每個 process 都會依照出現的順序執行 child plan。 使用 Parallel Append
時 executor 會盡量平均分散 child plan , 讓 child plan 同時執行。這樣可以避免競爭也避免啟動 child plan 的 process 但卻沒有使用到。
在 parallel plan 時,與一般的 Append
node 不同(只能有只能有 partial child plans ), Parallel Append
node 可以有 partial child plans 與 non-partial child plans 。 Non-partial children 只會被一個 process 掃描,因為掃超過一次會產生重複的結果。因此,即使沒有有效的 partial plan 可用,涉及附加多個 results set 的 plan 也可以實現粗粒度的並行性。例如,一個針對 partitioned table 的查詢,該查詢只能通過使用不支援 parallel scan 的 index 來有效率地實現。 planner 可能會選擇一般的 Index Scan
plan 的 Parallel Append
,每個單獨的 index scan 都會被一個 process 執行,但不同的 process 可以同時執行不同的掃描。
enable_parallel_append
可以關閉這個功能。
15.3.5. Parallel Plan Tips
如果 query 沒有生成預期的 parallel plan ,可以嘗試減小 parallel_setup_cost
或 parallel_tuple_cost
。 plan 也可能變得比 serial plan 慢,但不常發生。如果參數都設定很小或是零,還是沒有產生 parallel plan ,可能有別的原因,可以參考 Section 15.2
和 Section 15.4
。
執行 parallel plan 時可以使用 EXPLAIN (ANALYZE, VERBOSE)
查看給個 plan node 的每個 worker 的詳情。
15.4. Parallel Safety
planner 把操作分成 PARALLEL SAFE
PARALLEL RESTRICTED
PARALLEL UNSAFE
。
PARALLEL SAFE
不會和 parallel query 衝突。PARALLEL RESTRICTED
不能再 parallel worker 執行,但是當 parallel query 的時候可以在 leader 執行,因此PARALLEL RESTRICTED
不可能出現在Gather
node 或Gather Merge
node 之下,但可以出現在其他位置。PARALLEL UNSAFE
不能在 parallel query 執行,也不能在 leader 執行,當 query 包含任何PARALLEL UNSAFE
的操作, parallel query 就會完全禁用。
以下操作是 PARALLEL RESTRICTED
:
- CTE 的掃描
- temporary table 的掃描
- foreign tables 的掃描 (IsForeignScanParallelSafe 除外)
- 有 InitPlan 的 plan node
- 引用 SubPlan 的 plan node
15.4.1. Parallel Labeling for Functions and Aggregates
planner 不會自動判斷 user-defined 的 function 或 aggregate 是 PARALLEL SAFE
, PARALLEL RESTRICTED
, PARALLEL UNSAFE
,除非被標記不然都視為 PARALLEL UNSAFE
。使用 CREATE FUNCTION
或 ALTER FUNCTION
可以指定 PARALLEL SAFE
PARALLEL RESTRICTED
PARALLEL UNSAFE
。使用 CREATE AGGREGATE
時 PARALLEL
可以指定 SAFE
RESTRICTED
UNSAFE
。
如果 function 和 aggregate 會 寫入db
access sequence
改變 transaction 狀態
更改設定
那一定要被標為 PARALLEL UNSAFE
,通樣如果 function 會 access temporary tables
access client connection state
access cursors
... 等等或無法在 worker 同步的 backend-local state 那一定要被標為 PARALLEL RESTRICTED
。 例如 setseed
和 random
。
一般而言,如果一個 function 的平行標記虛報了,把他用在 parallel query 中可能會報錯或是拿到錯誤的答案;如果是 C 語言的 function 那會更嚴重。所以有任何疑慮,最好標為 UNSAFE
。
如果在 parallel worker 中的 function 要求一個不是 leader 鎖擁有的 lock ,例如 query 一張 query 沒有參照的 table ,則該 lock 會在 worker exit 的時候 release ,而不是 transaction 結束的時候。所以這種函數要被標為 PARALLEL RESTRICTED
讓他只在 leader 執行。
query planner 不會為了生成更好的 plan 而延遲評估 PARALLEL RESTRICTED
的 function 或 aggregate ,所以如果 WHERE
語句對一張 table 是 PARALLEL RESTRICTED
那 query planner 就不會在 parallel 的部分執行該 table 的掃描。有的情況下,在 parallel 的部分延遲評估 WHERE
語句是可行或是比較有效率的,但是 planner 不會這樣做。